<?php
namespace excel;

vendor('PhpExcel.PHPExcel');//引入phpexcel

use oss\Oss as aliyunoss;

class Excels
{
    /**
     * 导出Excle文件
     * @param  string    $array         数据列表
     * @param  string    $fileName      导出文件名称
     * @param  string    $img_column    第几列为图片（A）
     * @param  string    $width_column  第几列为最大宽（A,B）
     * @param  string    $save_type     保存方式（1浏览器 2文件）
     * @param  string    $min_width     列最小宽度
     * @param  string    $min_height    行最小高度
     * @param  string    $max_width     列最大宽度
     * @param  string    $max_height    行最大高度
     */
    public static function index($array, $fileName, $img_column = '', $width_column = '', $save_type = 1, $min_width = 10, $min_height = 30, $max_width = 30, $max_height = 75)
    {
        header("Content-Type:text/html;charset=utf-8");//设置utf-8编码格式
        $objPHPExcel = new \PHPExcel();

        $objActSheet = $objPHPExcel->getActiveSheet();

        $column = '1';
        $have_img = 0;
        foreach ($array as $key => $rows) 
        {
            $span = ord("A");
            foreach ($rows as $keyName => $value) 
            {
                $is_img = 0;
                if ($keyName < 26) {
                    $chr_span = chr($span);
                }elseif ($keyName == 26) {
                    $chr_span = 'AA';
                }elseif ($keyName == 27) {
                    $chr_span = 'AB';
                }else{
                }
                
                if (!empty($img_column)) {
                    if ($column > 1) {
                        if ($chr_span == $img_column) {
                            $is_img = 1;//图片
                            $have_img = 1;//有图片
                        }
                    }
                }

                //设置图片
                if ($is_img == 1) {
                    $file_uniqid = uniqid();
                    $img_path = self::download($value,'./uploads/'.$file_uniqid.'.jpg');
                    $objDrawing = new \PHPExcel_Worksheet_Drawing();
                    $objDrawing->setPath('./uploads/'.$file_uniqid.'.jpg');
                    $objDrawing->setHeight(80);
                    $objDrawing->setWidth(80);
                    $objDrawing->setCoordinates($chr_span . $column);
                    $objDrawing->setOffsetX(30);
                    $objDrawing->setOffsetY(10);
                    $objDrawing->getShadow()->setVisible(true);
                    $objDrawing->getShadow()->setDirection(45);
                    $objDrawing->setWorksheet($objActSheet);
                }else{
                    $objActSheet->setCellValue($chr_span . $column, $value);
                }

                //设置列的宽度
                if (!empty($width_column)) {
                    $width_column_array = explode(',', $width_column);
                    //指定列设置最大宽度(A,B 表示A B两列均设置最大宽度)
                    if (in_array($chr_span, $width_column_array)) {
                        $objActSheet->getColumnDimension($chr_span)->setWidth($max_width);
                    }else{
                        $objActSheet->getColumnDimension($chr_span)->setWidth($min_width);
                    }
                }else{
                    $objActSheet->getColumnDimension($chr_span)->setWidth($min_width);
                }

                // 设置行的高度
                if ($have_img == 1) {
                    if ($column > 1) {
                        $objActSheet->getRowDimension($column)->setRowHeight($max_height);
                    }else{
                        $objActSheet->getRowDimension($column)->setRowHeight($min_height);
                    }
                }else{
                    $objActSheet->getRowDimension($column)->setRowHeight($min_height);
                }

                if ($column == 1) {
                    //设置边框
                    $objBorder = $objActSheet->getStyle($chr_span . $column)->getBorders();
                    $objBorder->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN)->getColor()->setARGB('0006CD66');

                    //设置背景颜色
                    // $objPHPExcel->getActiveSheet()->getStyle($chr_span . $column)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('06CD66');

                    //设置字体
                    $objFont = $objActSheet->getStyle($chr_span . $column)->getFont();
                    $objFont->setSize(12);//字体大小
                    // $objFont->setBold(true);//是否加粗
                    // $objFont->getColor()->setARGB('FFFFFFFF');//设置颜色
                }

                //垂直居中 水平居中
                $objActSheet->getStyle($chr_span . $column)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $span++;
            }
            $column++;
        }

        //设置工作表名称
        // $objPHPExcel->getActiveSheet()->setTitle("渠道订单");

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');

        if ($save_type == 1) {
            //到浏览器
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            header("Content-Type: application/download");
            header('Content-Disposition:inline;filename="'.$fileName.'.xlsx"');
            header("Content-Transfer-Encoding: binary");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Pragma: no-cache");
            $objWriter->save('php://output');
        }else{
            //到文件
            $objWriter->save('./uploads/excel/'.$fileName.'.xlsx');

            $fileUrl = "/uploads/excel/".$fileName.".xlsx";
            $url = aliyunoss::getOssUrl('.'.$fileUrl, 'xlsx');
            apiReturn(200,'导出成功',$url);
        }
    }

    /**
     * 读取xls文件并返回数组
     * @param $url    图片路径
     * @return array
     */
    public static function readExcelFile($url, $type=1)
    {
        header("Content-Type:text/html;charset=utf-8");//设置utf-8编码格式
        if ($type == 1) {
            $urlInfo = explode('aliyuncs.com/', $url);
            $absolute_path = "./uploads/ossimg/".$urlInfo[1];
            download($url, $absolute_path);
        }else{
            $absolute_path = ".".$url;
        }

        $fileType=\PHPExcel_IOFactory::identify($absolute_path);
        $objReader=\PHPExcel_IOFactory::createReader($fileType);//创建phpexcel读取对象
        // $sheetName='Sheet1';
        // $objReader->setLoadSheetsOnly($sheetName);//加载指定sheet
        $objPHPExcel=$objReader->load($absolute_path);
        return $objPHPExcel->getActiveSheet()->toArray();
    }

}